<?php

/**
 * Description of dbtable
 * Definisce alcuni metodi standard per le operazioni CRUD su una specifica tabella
 *  Implementa l'Overloading
 *  Tutti i metodi che iniziano con __ sono dinamici, permette di settare e ottenere le proprietà dinamicamente
 *  ES: new Modello( array('proprietà'=> 'valore') setta automaticamente $modello->proprietà a 'valore'
 *  senza dover dichiarare i metodi specifici per ogni colonna della tabella
 * 
 * @author luigi.pastore
 * @created  10-mag-2011
 */
class Classes_DbTable_Table
{
    /**
     *
     * @var type 
     */
    protected   $_errors;
    
    /**
     * Connessione Db
     * @var type MysqliLink
     */
    protected	$_db;
    
    /**
     * Colonne della tabella
     * @var array 
     */
    protected	$_columns;

    /**
     * Filtri delle proprietà da passare a filter_var_array()
     * @var Array 
     */
    private	$_filters = array();

    /**
     * Risultati delle Query
     * @var type 
     */
    private	$_result;

    /**
     * Nome chiave primaria
     * @var string 
     */
    protected	$_primary;
    
    /**
     * Nome colonna created (TIMESTAMP)
     * @var string 
     */
    private $_created;
    
    /**
     * Nome colonna created_by (INT)
     * @var string 
     */
    private $_createdby;
    
    /**
     * Nome colonna modifies_by (INT)
     * @var string 
     */
    private $_modifiedby;


    public function __construct( array $data = null )
    {
        // Imposto il nome della tabella col prefisso definito in costanti.ini
        $this->_table = DBTABLE_PREFIX . $this->_table;

	// Connessione DB...
	$this->_db = Classes_Core_MySqliLayer::getInstance();
	
	$sql = $this->_db->query( 'SELECT * FROM ' . $this->_table .' LIMIT 1' );
	
	// Ottiene Info sulle colonne della tabella
	$columns = $sql->fetch_fields();

	foreach( $columns as $column )
	{
	    // Setta la chiave Primaria e la usa come key nell'array restituito...
	    if ( !$this->_primary )
	    {
		if( $column->flags & MYSQLI_PRI_KEY_FLAG )
		    $this->_primary = $column->name;
	    }
	    
	    // Cerca i nomi della colonna created per *settare* automaticamente il valore in caso di inserimento
	    if( substr( $column->name, -7 ) == 'created' )
		$this->_created = $column->name;
	    
	    // Cerca i nomi della colonna createdby per *leggere* automaticamente il valore in caso di inserimento
	    if( substr( $column->name, -9 ) == 'createdby' )
		$this->_createdby = $column->name;
	    
	    // Cerca i nomi della colonna modifiedby per *leggere* automaticamente il valore in caso di inserimento
	    if( substr( $column->name, -10 ) == 'modifiedby' )
		$this->_modifiedby = $column->name;
	    
	    $this->_columns[$column->name] = $column;
	}
        
        // Se vengono passate le proprietà, settiamole
	if( is_array( $data ) )
	{
	    $this->setOptions( $data );
	}
    }
        
    /*
     *
     */
    public function __call( $name, $arguments )
    {
	if( substr( $name, 0, 3 ) == "set" )
	{
	    return $this->__set( substr( $name, 3 ), $arguments[0] );
	}
	if( substr( $name, 0, 3 ) == "get" )
	{
	    return $this->__get( substr( $name, 3 ) );
	}
	
//	if(  method_exists( $this, $name ))
//	{
//	    return $this->$name();
//	}
	
	throw new Exception( 'Metodo ' . $name . ' non Supportato' );
    }

    public function __get( $name )
    {
	$propertyName = '_'. strtolower( $name );
	
	if( property_exists( $this, $propertyName ))
	{
	    return $this->$propertyName;
	}
	elseif( !array_key_exists(  strtolower( $name ), $this->_columns ) )
	{
	    throw new Exception( '(Get)Proprietà invalida: ' . $name );
	}
	else
	{
	    return $this->$name;
	}	
    }
    
    public function __set( $name, $value )
    {
	// Non settare le proprietà che iniziano con _
	if( property_exists( $this, $name ) AND substr( $name, 0, 1 ) == "_" )
	{
	    return;
	}
	elseif( !@array_key_exists( strtolower( $name ), $this->_columns ) )
	{
	    throw new Exception( '(Set)Proprietà invalida: ' . var_dump( $name ) );
	}
	else
	{
	    $this->$name = $value;
	}
	return $this;
    }


    public function setOptions( array $options )
    {
	//$methods = get_class_methods( $this );

	foreach( $options as $key => $value )
	{
	    $method = 'set' . ( $key );

	    $this->$method( $value );
	}
	return $this;
    }

    /*
     * Restituisce l'array delle proprietà SETTATE dell'oggetto
     */
    public function toArray()
    {
	$values = array( );
	foreach( get_object_vars( $this ) as $pkey => $pvalue )
	{
	    if( substr( $pkey, 0, 1 ) != "_" && isset( $this->$pkey ) && ( $this->$pkey !== '' ) )
	    {
		$call = "get" . $pkey;
		$values[$pkey] = $this->$call();
	    }
	}

	return $values;
    }  

    /**
     * Restituisce le proprietà definibili dall'utente.
     * @return array 
     */
    private function getProperties()
    {
	$ret = array();
	
	$properties = get_object_vars( $this );
	
	foreach( $properties as $propertyName => $propertyValue )
	{
	    if(  substr( $propertyName, 0, 1 ) != '_' AND ( $propertyValue !== '' ) )
	    {
		$ret[$propertyName] = $propertyValue;
	    }
	}
	return $ret;
    }

    /**
     * Setta i filtri per tutte le proprietà
     * @param array $filters 
     */
    public function setFilters( array $filters )
    {
	$this->_filters = $filters;
    }

    /**
     * Setta un filtro per una proprietà.
     * @param type $property
     * @param array $filter Filtro nel formato array('filter' => ..., 'flags' => ..., 'options' => ...)
     */
    public function setFilter( $property, $filter )
    {
	$this->_filters[$property] = $filter;
    }

    /**
     * Restituisce i filtri
     * @return type Array
     */
    private function getFilters()
    {
	if( is_array( $this->_filters ) )
	    return $this->_filters;

	return array( );
    }

    /**
     * Filtra una data nel formato yyyy-mm-dd hh:mm:ss
     * 
     * @param type $date
     */
    protected function filterDate( $date )
    {
	$timestamp = strtotime( $date );
        $data = explode( ' ', $date );
        list( $y, $m, $d ) = explode( '-', $data[0] );

	if( $timestamp === false )
            return false;
	    //throw new Classes_Exceptions( 'Formato Data invalido: '. $date );

        if( !is_string( $d ) AND !is_string( $m ) AND !is_string( $y ) AND !checkdate( $m, $d, $y ) )
            return false;
        
	return date( 'Y-m-d H:i:s', $timestamp );
    }

    /**
     * Filtra un testo
     * @param type $text 
     */
    private function filterText( $text )
    {
	//$text = filter_var( $text, FILTER_SANITIZE_SPECIAL_CHARS, array( 'flags' => FILTER_FLAG_STRIP_LOW ) );
	$text = filter_var( $text, FILTER_SANITIZE_SPECIAL_CHARS );

	return str_replace( array( "\r\n", "\r", "\n" ), "", $text );
    }

    /**
     * Begin a transaction.
     *
     * @return void
     */
    public function beginTransaction()
    {
        $this->_db->_beginTransaction();
    }

    /**
     * Commit a transaction.
     *
     * @return void
     */
    public function commit()
    {
        $this->_db->_commit();
    }

    /**
     * Roll-back a transaction.
     *
     * @return void
     */
    public function rollBack()
    {
        $this->_db->_rollBack();
    }
    
    /**
     * Esegue il fetch di id multipli
     * @param array $id
     * @param type $join
     * @return array
     * @throws Classes_Exceptions 
     */
    public function fetchById( array $id, $params = array() )
    {
	if( !is_array( $id ) AND count( $id ) < 1 )
	    throw new Classes_Exceptions( 'Id Invalidi: '. $id );
	
	$sql_data = array( 'from' => $this->_table, // tabella
            'fields' => array( '*' ), //
            //'join' => $join, // Left Join
            'where' => array( $this->_primary => $id ), // where
        );
	
        $sql_data = array_merge( $sql_data, $params );
        
	// Formatta, se ci sono, le colonne DATETIME
	foreach( $this->_columns AS $column )
	{
	    if( $column->type == MYSQLI_TYPE_DATETIME OR $column->type == MYSQLI_TYPE_TIMESTAMP )
	    {
		$sql_data['fields'] = array_merge( $sql_data['fields'], array( 'DATE_FORMAT( ' . $column->name . ' ,\'%d-%m-%Y %H:%i:%s\') AS ' . $column->name . '_ita' ) );
	    }
	    
	    if( $column->type == MYSQLI_TYPE_DATE )
	    {
		$sql_data['fields'] = array_merge( $sql_data['fields'], array( 'DATE_FORMAT( ' . $column->name . ' ,\'%d-%m-%Y\') AS ' . $column->name . '_ita' ) );
	    }	    
	    
	    if( $column->type == MYSQLI_TYPE_TIME )
	    {
		$sql_data['fields'] = array_merge( $sql_data['fields'], array( 'DATE_FORMAT( ' . $column->name . ' ,\'%Hh%i\') AS ' . $column->name . '_ita' ) );
	    }
	}
        
	$this->_result = $this->_db->selectQuery( $sql_data );
	
        if( $this->_result->num_rows > 0 )
	{
	    while( $data = $this->_result->fetch_assoc() )
	    {
		$ret[$data[$this->_primary]] = $data;
                
                // Aggiungo i dati dell'utente relativo a createdby e modifiedby...
                if( !empty( $data[$this->_createdby] ) )
                {
                    $ret[$data[$this->_primary]]['created_by'] = $this->_db->fetch( $this->_db->simpleSelectQuery( array( 0 => 'fab_users', 1 => '*', 2 => array( 'user_id' => $data[$this->_createdby] ) ) ) );
                }

                if( !empty( $data[$this->_modifiedby] ) )
                {
                    $ret[$data[$this->_primary]]['modified_by'] = $this->_db->fetch( $this->_db->simpleSelectQuery( array( 0 => 'fab_users', 1 => '*', 2 => array( 'user_id' => $data[$this->_modifiedby] ) ) ) );
                }
	    }
	}
        
	return $ret;
    }
    
    /**
     * Prende i parametri dal modello padre, SE SETTATI,
     * altrimenti compila i parametri in base ai tipi colonna, secondo un modello
     * standard.
     * 
     * Esempio:
     * $params = array(
     *		'orderby' => array( 'campo' => 'ordine' ),
     *		'limit' => array( 'start', 'end' )		
     *		)
     * 
     * @param array $params
     * @return type 
     */
    public function fetchAll( array $params = array() )
    {
	// OrderBy
	if( $params['orderby'] )
	{
	    $orderby_field = key( $params['orderby'] );
	    $orderby_dir = $params['orderby'][$orderby_field];

//	    if( !array_key_exists( $orderby_field, $this->_columns ) OR !in_array( strtoupper( $orderby_dir ), array( 'ASC', 'DESC' ) ) )
//		throw new Classes_Exceptions( 'Campo ORDERBY Invalido: '. $orderby_field .' - '. $orderby_dir );
	    if( !in_array( strtoupper( $orderby_dir ), array( 'ASC', 'DESC' ) ) )
		throw new Classes_Exceptions( 'Direzione ORDERBY Invalido: '. $orderby_field .' - '. $orderby_dir );
	}

	
	$sql_data = array( 'from' => $this->_table, // tabella
	    'fields'	=> $params['fields'] ? $params['fields'] : array( '*' ), //
	    'join'	=> $params['join'] ? $params['join'] : NULL,
	    'orderby'	=> ( count( $params['orderby'] ) > 0 ) ? $params['orderby'] : NULL,
	    'limit'	=> ( count( $params['limit'] ) == 2 ) ? $params['limit'] : NULL,
            'where'     => $params['where']
	);

	// Formatta, se ci sono, le colonne DATETIME
	foreach( $this->_columns AS $column )
	{
	    if( $column->type == MYSQLI_TYPE_DATETIME OR $column->type == MYSQLI_TYPE_TIMESTAMP )
	    {
		$sql_data['fields'] = array_merge( $sql_data['fields'], array( 'DATE_FORMAT( ' . $column->name . ' ,\'%d-%m-%Y %H:%i:%s\') AS ' . $column->name . '_ita' ) );
	    }
	    
	    if( $column->type == MYSQLI_TYPE_DATE )
	    {
		$sql_data['fields'] = array_merge( $sql_data['fields'], array( 'DATE_FORMAT( ' . $column->name . ' ,\'%d-%m-%Y\') AS ' . $column->name . '_ita' ) );
	    }
            
	    if( $column->type == MYSQLI_TYPE_TIME )
	    {
		$sql_data['fields'] = array_merge( $sql_data['fields'], array( 'DATE_FORMAT( ' . $column->name . ' ,\'%Hh%i\') AS ' . $column->name . '_ita' ) );
	    }            
	}
	
	$this->_result = $this->_db->selectQuery( $sql_data );

	if( $this->_result->num_rows > 0 )
	{
	    while( $data = $this->_result->fetch_assoc() )
	    {
		$ret[$data[$this->_primary]] = $data;
                
                // Aggiungo i dati dell'utente relativo a createdby e modifiedby...
                if( !is_null( $this->_createdby ) AND !is_null( $data[$this->_createdby] ) )
                {
                    $ret[$data[$this->_primary]]['created_by'] = $this->_db->fetch( $this->_db->simpleSelectQuery( array( 0 => 'fab_users', 1 => '*', 2 => array( 'user_id' => $data[$this->_createdby] ) ) ) );
                }

                if( !is_null( $this->_modifiedby ) AND !is_null( $data[$this->_modifiedby] ) )
                {
                    $ret[$data[$this->_primary]]['modified_by'] = $this->_db->fetch( $this->_db->simpleSelectQuery( array( 0 => 'fab_users', 1 => '*', 2 => array( 'user_id' => $data[$this->_modifiedby] ) ) ) );
                }                
	    }
	}

	return $ret;
    }
    
    /**
     * Restituisce il totale delle righe presenti sul db.
     * Prende in input i parametri (opzionale)
     * 
     * @return type 
     */
    public function getCount( $params = array() )
    {
	$this->_result = $this->_db->selectQuery( array(
	    'from' => $this->_table,
	    'fields' => 'COUNT('. $this->_primary .')',
            'where' => $params
	) );
	
	$tot = $this->_result->fetch_row();
	
	return $tot[0];
    }


    /**
     *
     * @return Classes_DbTable_Table 
     */
    public function save()
    {

//	/**
//	 * Cicla le proprietà settate dell'oggetto e fa un controllo basato sul
//	 * tipo di colonna del db.
//	 */
//	$properties = $this->getProperties();
//	$filters    = $this->getFilters();
//        
//	# Controlla i campi NOT NULL
//	# Lancia un'eccezione se viene lasciato nullo qualsiasi campo eccetto quello primario e i timestamp created e modified
//	foreach( $this->_columns AS $column )
//	    if( ( $column->flags & MYSQLI_NOT_NULL_FLAG ) AND empty( $properties[$column->name] ) AND !( $column->flags & MYSQLI_AUTO_INCREMENT_FLAG ) AND !( $column->type & MYSQLI_TYPE_TIMESTAMP ) )
//		throw new Classes_Exceptions( 'CAMPO NOT NULL: ' . $column->name . ' - ' . $properties[$column->name] );
//	
//	foreach( $properties as $propertyName => $propertyValue )
//	{
//	    # Setta a NULL tutti i campi vuoti
//	    if( $propertyValue == '' OR empty( $propertyValue ) )
//		$this->$propertyName = NULL;
//	    
//            # Se la proprietà è un oggetto SqlExpression, andiamo oltre...
//            if( $this->$propertyName instanceof Classes_SqlExpression )
//                continue;
//            
//	    switch( $this->_columns[$propertyName]->type )
//	    {
//		case MYSQLI_TYPE_DATETIME:
//		case MYSQLI_TYPE_DATE:
//		case MYSQLI_TYPE_TIMESTAMP:
//		    if( strtotime( $this->$propertyName ) === false )
//			throw new Classes_Exceptions( 'Formato Data invalido: '. $propertyName .' - '. $this->$propertyName );
//
//		    if( !$filters[$propertyName] )
//			$filters[$propertyName] = array( 'filter' => FILTER_CALLBACK, 'options' => array( $this, 'filterDate' ) );
//
//		break;
//
//		case MYSQLI_TYPE_LONG:
//		    if( is_numeric( $this->$propertyName ) === false )
//			throw new Classes_Exceptions( 'Formato Intero invalido: '. $propertyName .' - '. $this->$propertyName );			    
//
//		    if( !$filters[$propertyName] )
//			$filters[$propertyName] = FILTER_SANITIZE_NUMBER_INT;
//
//		break;
//		
//		case MYSQLI_TYPE_VAR_STRING:
//		case MYSQLI_TYPE_BLOB:
//		    if( !$filters[$propertyName] )
//			$filters[$propertyName] = array( 'filter' => FILTER_CALLBACK, 'options' => array( $this, 'filterText' ) );
//
//		    break;
//
//		default:
//		    if( !$filters[$propertyName] )
//			$filters[$propertyName] = FILTER_SANITIZE_STRING;
//
//		    break;
//	    }
//	}
        
	# Filtriamo...
        if( $this->isValid() )
        {
            $this->setOptions( ( filter_var_array( $this->toArray(), $this->getFilters() ) ) );

            if( !is_null( $this->{$this->_primary} ) )
            {
                // UPDATE
                if( !is_numeric( $this->{$this->_primary} ) )
                    throw new Classes_Exceptions( 'Edit: Id invalido '. $this->{$this->_primary} );

                // Togliamo la chiave primaria dai dati da inviare all'update
                $data = $this->toArray();

                unset( $data[$this->_primary] );

                $sql_data = array( $this->_table, $data, array( 
                                                        $this->_primary => $this->{$this->_primary} 
                                                        )
                                                    );

                # Esegui Query...
                if( is_numeric( $this->_db->updateQuery( $sql_data ) ) )
                    return $this;
                else
                    $this->{$this->_primary} = null;
            }
            else
            {
                // INSERT
                // Setta la colonna created a CURRENT_TIMESTAMP
                if( $this->_created )
                    $this->{$this->_created} = $this->filterDate( 'now' );

                $sql_data = array( $this->_table, $this->toArray() );

                # Esegui Query...
                $this->{$this->_primary} = $this->_db->insertQuery( $sql_data );
            }

            return $this;            
        }
        else
        {
            return $this->_errors;
        }
    }
    
    public function delete()
    {
        if( !is_numeric( $this->{$this->_primary} ) )
            throw new Classes_Exceptions( 'DELETE: Id invalido '. $this->{$this->_primary} );

        $sql_data = array( $this->_table, array( 
                                                $this->_primary => $this->{$this->_primary} 
                                                )
                                            );            
        # Esegui Query...
        if( is_numeric( $affected_rows = $this->_db->deleteQuery( $sql_data ) ) )
            return $affected_rows;
        else
            $this->{$this->_primary} = null;
    }

    /**
     * BETA!
     * @todo implementare validazione
     * @return type 
     */
    protected function isValid()
    {
	/**
	 * Cicla le proprietà settate dell'oggetto e fa un controllo basato sul
	 * tipo di colonna del db.
	 */
	$properties = $this->getProperties();
	$filters    = $this->getFilters();

	# Controlla i campi NOT NULL
	# Lancia un'eccezione se viene lasciato nullo qualsiasi campo eccetto quello primario e i timestamp 'created' e 'modified'
	foreach( $this->_columns AS $column )
        {
//	    if( empty( $this->{$this->_primary} ) AND ( $column->flags & MYSQLI_NOT_NULL_FLAG ) AND ( $properties[$column->name] == '' ) AND !( $column->flags & MYSQLI_AUTO_INCREMENT_FLAG ) AND ( $column->type !== MYSQLI_TYPE_TIMESTAMP ) )
//                $this->_errors[$column->name] = true;
	    if( ( $column->name != $this->{$this->_primary} ) AND ( $column->flags & MYSQLI_NOT_NULL_FLAG ) AND ( $properties[$column->name] == '' ) AND !( $column->flags & MYSQLI_AUTO_INCREMENT_FLAG ) AND ( $column->type !== MYSQLI_TYPE_TIMESTAMP ) )
                $this->_errors[$column->name] = true;

            if( !empty( $this->{$this->_primary} ) )
            {
                unset( $this->_errors[$this->_createdby] );
                unset( $properties[$this->_createdby] );
            }
            
//            if( ( $column->flags & MYSQLI_NOT_NULL_FLAG ) AND ( $properties[$column->name] == '' ) )
//                $this->_errors[$column->name] = true;
        }

        # CreatedBy e ModifiedBy
        if( is_null( $this->{$this->_primary} ) ):
	    if( $this->_createdby AND empty( $this->{$this->_createdby} ) ):
		$this->_errors[$this->_createdby] = 'Createdby Invalido ';
            endif;
	else:
	    if( $this->_modifiedby AND empty( $this->{$this->_modifiedby} ) ):
		$this->_errors[$this->_modifiedby] = 'Modifiedby Invalido ';
            endif;
        endif;

	foreach( $properties as $propertyName => $propertyValue )
	{
            # Bypassa i filtri su valori nulli su colonne NULL (serve a permettere di filtrare solo se effettivamente passato un valore)
            if( $propertyValue == '' AND  !( $this->_columns[$propertyName]->flags & MYSQLI_NOT_NULL_FLAG ) )
                continue;
            
	    # Setta a NULL tutti i campi vuoti
	    if( $propertyValue == '' )
		$this->$propertyName = NULL;
	    
            # Se la proprietà è un oggetto SqlExpression, andiamo oltre...
            if( $this->$propertyName instanceof Classes_SqlExpression )
                continue;

	    switch( $this->_columns[$propertyName]->type )
	    {
		case MYSQLI_TYPE_DATETIME:
		case MYSQLI_TYPE_DATE:
		case MYSQLI_TYPE_TIMESTAMP:
                case MYSQLI_TYPE_TIME:
//		    if( strtotime( $this->$propertyName ) === false )
//                        $this->_errors[$propertyName] = 'Formato Data invalido: '. $propertyName .' - '. $this->$propertyName;
//			throw new Classes_Exceptions( 'Formato Data invalido: '. $propertyName .' - '. $this->$propertyName );

                    // Nel caso di date non obbligatorie bypassiamo il filtro...
		    if( !$filters[$propertyName] )
			$this->_filters[$propertyName] = array( 'filter' => FILTER_CALLBACK, 'options' => array( $this, 'filterDate' ) );

		break;

		case MYSQLI_TYPE_LONG:
		case MYSQLI_TYPE_TINY:
//		    if( is_numeric( $this->$propertyName ) === false )
//                        $this->_errors[$propertyName] = 'Formato Intero invalido: '. $propertyName .' - '. $this->$propertyName;
//			throw new Classes_Exceptions( 'Formato Intero invalido: '. $propertyName .' - '. $this->$propertyName );		    

		    if( !$filters[$propertyName] )
			$this->_filters[$propertyName] = FILTER_VALIDATE_INT;

		break;
		
		case MYSQLI_TYPE_VAR_STRING:
		case MYSQLI_TYPE_BLOB:
		    if( !$filters[$propertyName] OR $filters[$propertyName] == NULL )
			$this->_filters[$propertyName] = array( 'filter' => FILTER_CALLBACK, 'options' => array( $this, 'filterText' ) );

		    break;

		default:
		    if( !$filters[$propertyName] AND $filters[$propertyName] !== NULL )
			$this->_filters[$propertyName] = FILTER_SANITIZE_STRING;

		    break;
            }
	}

        $filtered_data = filter_var_array( $this->toArray(), $this->_filters );

        foreach( $filtered_data as $key => $value )
        {
            if( $value === FALSE )
            {
                $this->_errors[$key] = TRUE;
            }
        }

        return count( $this->_errors ) ? false : true;
    }
}
